from flask import Flask, request
from sqlserver import connect
from collections import defaultdict
import random
import datetime

app = Flask(__name__)


@app.route("/login", methods=["POST"])
def login():
    data = request.json
    tu = tuple(value for value in data.values())
    con = connect()

    with con.cursor() as cursor:
        sql = "SELECT * FROM users where username=%s and password=%s"
        cursor.execute(sql, tu)
        result = cursor.fetchone()
        print(result)
        if result == None:
            con.close()
            return {"message": "账号或密码错误"}
        else:
            return result


@app.route("/register", methods=["POST"])
def register():
    data = request.json
    username = data.get("username")
    password = data.get("password")
    phoneNumber = data.get("phoneNumber")
    con = connect()

    with con.cursor() as cursor:
        qu_user = "select username from users where username=%s"
        cursor.execute(qu_user, (username,))
        queryResult = cursor.fetchone()

        insert_user = (
            "insert into users (username, password, phoneNumber)values(%s, %s, %s)"
        )

        if queryResult == None:
            cursor.execute(insert_user, (username, password, phoneNumber))
            con.commit()
            con.close()
            return {"message": "注册成功"}
        else:
            con.close()
            return {"message": "账号已经存在"}


@app.route("/getUsers", methods=["GET"])
def getUser():
    con = connect()
    with con.cursor() as cursor:
        querySql = "select * from users"
        cursor.execute(querySql)
        result = cursor.fetchall() or []
        con.close()
        return result


@app.route("/addUser", methods=["POST"])
def addUser():
    data = request.json
    tu = tuple(val for val in data.values())
    con = connect()

    with con.cursor() as cursor:
        query_sql = "select * from users where username=%s"
        cursor.execute(query_sql, (tu[0],))
        if cursor.fetchone():
            return {"message": "用户已经存在"}
        sql = "insert into users(username, password, phoneNumber, role, permissions)values(%s, %s, %s, %s, %s)"
        cursor.execute(sql, tu)
        con.commit()
        con.close()
    return {"message": "添加成功"}


@app.route("/updateUser", methods=["POST"])
def updateUser():
    data = request.json
    id = data.get("id")
    username = data.get("username")
    password = data.get("password")
    phoneNumber = data.get("phoneNumber")
    role = data.get("role")
    permissions = data.get("permissions")

    con = connect()
    with con.cursor() as cursor:
        sql = "update users set username=%s, password=%s, phoneNumber=%s, role=%s, permissions=%s where id=%s"
        cursor.execute(sql, (username, password, phoneNumber, role, permissions, id))
        con.commit()
        con.close()
    return {"message": "更新成功"}


@app.route("/deleteUser", methods=["POST"])
def deleteUser():
    id = request.json.get("id")
    con = connect()
    with con.cursor() as cursor:
        sql = "delete from users where id=%s"
        cursor.execute(sql, (id,))
        con.commit()
        con.close()
    return {"message": "删除成功"}


@app.route("/getPer", methods=["GET"])
def getPer():
    per = request.args.get("per")
    con = connect()
    with con.cursor() as cursor:
        querySql = "select * from router where permissions=%s"
        cursor.execute(querySql, (per,))
        result = cursor.fetchall() or []
        con.close()
        return result


@app.route("/getPers", methods=["GET"])
def getPers():
    con = connect()
    with con.cursor() as cursor:
        querySql = "select * from router"
        cursor.execute(querySql)
        result = cursor.fetchall() or []
        con.close()
        return result


@app.route("/addPer", methods=["POST"])
def addPer():
    data = request.json
    path = data.get("path")
    permissions = data.get("permissions")
    actions = data.get("actions")

    con = connect()
    with con.cursor() as cursor:
        sql = "insert into router(path, permissions, actions) values(%s, %s, %s)"
        cursor.execute(sql, (path, permissions, actions))
        con.commit()
        con.close()

    return {"message": "添加成功"}


@app.route("/deletePer", methods=["POST"])
def deletePer():
    id = request.json.get("id")
    con = connect()
    with con.cursor() as cursor:
        sql = "delete from router where id=%s"
        cursor.execute(sql, (id,))
        con.commit()
        con.close()
    return {"message": "删除成功"}


@app.route("/updatePer", methods=["POST"])
def updatePer():
    data = request.json
    id = data.get("id")
    path = data.get("path")
    permissions = data.get("permissions")
    actions = data.get("actions")

    con = connect()
    with con.cursor() as cursor:
        sql = "update router set path=%s, permissions=%s, actions=%s where id=%s"
        cursor.execute(sql, (path, permissions, actions, id))
        con.commit()
        con.close()

    return {"message": "更新成功"}


@app.route("/getdevice", methods=["GET"])
def getdevice():
    con = connect()
    with con.cursor() as cursor:
        sql = "select * from devices"
        cursor.execute(sql)
        result = cursor.fetchall() or []
        con.close()
        return result


@app.route("/addDevice", methods=["POST"])
def addDevice():
    data = request.json
    generation_type = data.get("generation_type")
    power_output = data.get("power_output")
    voltage = data.get("voltage")
    current = data.get("current")

    con = connect()
    with con.cursor() as cursor:
        sql = "insert into devices set generation_type=%s, power_output=%s, voltage=%s, current=%s"
        cursor.execute(sql, (generation_type, power_output, voltage, current))
        con.commit()
        con.close()

    return {"message": "添加成功"}


@app.route("/updateDevice", methods=["POST"])
def updateDevice():
    data = request.json
    id = data.get("id")
    generation_type = data.get("generation_type")
    power_output = data.get("power_output")
    voltage = data.get("voltage")
    current = data.get("current")

    con = connect()
    with con.cursor() as cursor:
        sql = "update devices set generation_type=%s, power_output=%s, voltage=%s, current=%s where id=%s"
        cursor.execute(sql, (generation_type, power_output, voltage, current, id))
        con.commit()
        con.close()
        return {"message": "修改成功"}


@app.route("/delDevice", methods=["POST"])
def delDevice():
    id = request.json.get("id")
    con = connect()
    with con.cursor() as cursor:
        sql = "delete from devices where id=%s"
        cursor.execute(sql, (id,))
        con.commit()
        con.close()
        return {"message": "删除成功"}


@app.route("/updateDeviceState", methods=["POST"])
def updateDeviceState():
    id = request.json.get("id")
    state = request.json.get("device_state")
    con = connect()
    with con.cursor() as cursor:
        updateSql = "update devices set device_state=%s where id=%s"
        insert = "insert into device_power_info (device_id, total_power_generation, generation_time)values(%s, %s, CURRENT_DATE())"
        cursor.execute(updateSql, (state, id))
        cursor.execute(insert, (id, random.randint(1, 999)))
        con.commit()
        con.close()

    return ""


@app.route("/getStatistics", methods=["POST"])
def getStatistics():
    data = request.json
    device_id = data.get("device_id")
    one_month_ago = datetime.datetime.now() - datetime.timedelta(days=30)

    con = connect()
    with con.cursor() as cursor:
        sql = "select * from device_power_info where device_id=%s"
        cursor.execute(sql, (device_id,))
        results = cursor.fetchall() or []
        cursor.close()
        stage = {}
        for row in results:
            generation_time = str(row["generation_time"])

            if generation_time in stage:
                stage[generation_time] += row["total_power_generation"]
            else:
                stage[generation_time] = row["total_power_generation"]

        return stage


if __name__ == "__main__":
    app.run(debug=True, port=8000)
